﻿using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class SqlSugarHelper
    {
        public static SqlSugarScope Db = new SqlSugarScope(new ConnectionConfig()
        {
            ConnectionString = @"server=localhost;Database=etest;Uid=root;Pwd=Olive123;",//连接符字串
            DbType = SqlSugar.DbType.MySql,//数据库类型
            IsAutoCloseConnection = true //不设成true要手动close
        },
        db => {
            //(A)全局生效配置点，一般AOP和程序启动的配置扔这里面 ，所有上下文生效
            //调试SQL事件，可以删掉
            db.Aop.OnLogExecuting = (sql, pars) =>
            {
                Console.WriteLine(sql);//输出sql,查看执行sql 性能无影响

                //获取原生SQL推荐 5.1.4.63  性能OK
                //UtilMethods.GetNativeSql(sql,pars)

                //获取无参数化SQL 对性能有影响，特别大的SQL参数多的，调试使用
                //UtilMethods.GetSqlString(DbType.SqlServer,sql,pars)

            };

            //多个配置就写下面
            //db.Ado.IsDisableMasterSlaveSeparation=true;

            //注意多租户 有几个设置几个
            //db.GetConnection(i).Aop
        });
        //(B)当前上下文生效配置点,一般多租户相关操作写在这儿 
        //db.GetConnectionxxx 只在当前上下文有效，不会共享
        //如果是程序启动时的配置全部到A里面去

        /// <summary>
        /// 查询数据库中所有数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> QueryAll<T>() where T : class, new()
        {
            return Db.Queryable<T>().ToList();
        }

        /// <summary>
        /// 根据主键ID返回单条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public T QueryOne<T>(int id) where T : class, new()
        {
            return Db.Queryable<T>().InSingle(id);

        }

        /// <summary>
        /// 根据搜索条件，返回单条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression"></param>
        /// <returns></returns>
        public T QueryOne<T>(Expression<Func<T, bool>> expression) where T : class, new()
        {
            return Db.Queryable<T>().Single(expression);
        }

        /// <summary>
        /// 更新List记录内容
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ts"></param>
        /// <returns></returns>
        public bool UpdateAll<T>(List<T> ts) where T : class, new()
        {
            return Db.Updateable<T>(ts).ExecuteCommand() > 0;
        }

        /// <summary>
        /// 插入单条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Insert<T>(T entity) where T : class, new()
        {
            return Db.Insertable<T>(entity).ExecuteCommand() > 0;
        }
        /// <summary>
        /// 通过主键ID删除单条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete<T>(int id) where T : class, new()
        {
            return Db.Deleteable<T>(id).ExecuteCommand() > 0;
        }

        public DataTable QueryDataToTable<T>(bool checkProductName, Expression<Func<T, bool>> expProductname, bool checkStartTime, Expression<Func<T, bool>> expStartTime, bool checkEndTime, Expression<Func<T, bool>> expEndTime, bool checkCode, Expression<Func<T, bool>> expCode)
        {
            var query = Db.Queryable<T>();

            if (checkProductName)
                query.Where(expProductname);
            if (checkStartTime)
                query.Where(expStartTime);
            if (checkEndTime)
                query.Where(expEndTime);
            if (checkCode)
                query.Where(expCode);
            return query.Clone().ToDataTable();


        }
        /// <summary>
        /// 当Dictionary<Expression<Func<T, bool>>,bool>中，bool型为真时，检查Expression条件
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dict"></param>
        /// <returns></returns>
        public DataTable QueryDataToTable<T>(Dictionary<Expression<Func<T, bool>>, bool> dict)
        {
            var query = Db.Queryable<T>();
            foreach (var item in dict)
            {
                if (item.Value)
                    query.Where(item.Key);
            }
            return query.Clone().ToDataTable();
        }
    }
}
